package com.central.apps.handler.purchase;

import cn.hutool.core.map.MapUtil;
import com.central.app.common.base.BaseHandler;
import com.central.app.common.cache.CfgRedisCache;
import com.central.app.common.constant.AppConstant;
import com.central.app.handler.factory.HandlerFactory;
import com.central.app.model.BizApp;
import com.central.app.model.BizObject;
import com.central.app.model.CommonModel;
import com.central.app.service.HandlerService;
import com.central.app.service.VersionService;
import com.central.apps.service.TableService;
import com.central.bizcommon.model.flow.FlowNode;
import com.central.bizcommon.model.flow.FlowOpinion;
import com.central.common.auth.details.LoginAppUser;
import com.central.common.util.SysUserUtil;
import com.central.common.util.ToolUtil;
import com.central.common.web.PageResult;
import com.central.common.web.Result;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * 采购需求变更业务逻辑
 *
 * @author 李大超
 * @date 2022/10/17
 */
@Component
@Slf4j
public class PurchaseRequestChangeHandler extends BaseHandler {
    @Autowired
    private HandlerService handlerService;
    @Autowired
    private CfgRedisCache cfgRedisCache;
    @Autowired
    private TableService tableService;
    @Autowired
    private VersionService versionService;

    @Override
    public String validateSave(String app, String objName, Map<String, Object> data) throws Exception {
        // 校验采购申请变更行信息
        if ("gq_prlinechange".equals(objName)) {
            String id = MapUtils.getString(data, "id");

            if (StringUtils.isEmpty(id)) {
                String prcid = MapUtil.getStr(data, "prcid");
                String prlid = MapUtil.getStr(data, "prlid");
                String flag = MapUtil.getStr(data, "flag");

                if (!"新增".equals(flag)) {
                    // 查询采购申请变更行信息
                    List<Map<String, Object>> prlcList = handlerService.findBySql("select id from gq_prlinechange where flag != '新增' and prlid = " + prlid + " and prcid = " + prcid);

                    if (CollectionUtils.isNotEmpty(prlcList)) {
                        return "采购需求行信息一个变更请求只允许一次变更操作";
                    }
                }
            }
        }

        if ("gq_prchange".equals(objName)) {
            Integer id = MapUtils.getInteger(data, "id");
            Integer prId = MapUtils.getInteger(data, "prid");
            StringBuilder sql = new StringBuilder();
            sql.append("select id from gq_prchange where status not in ('已批准', '已废弃') and prid = ").append(prId);
            if (id != null) {
                sql.append(" and id != ").append(id);
            }

            List<Map<String, Object>> list = handlerService.findBySql(sql.toString());
            if (CollectionUtils.isNotEmpty(list)) {
                return "已存在针对同样采购申请的变更申请, 请完成之前的申请!";
            }
        }
        return null;
    }

    @Override
    public void beforSave(String app, String objName, Map<String, Object> data, boolean isUpdate) throws Exception {
        if ("gq_prchange".equals(objName)) {
            String prcId = MapUtils.getString(data, "id");
            String prId = MapUtils.getString(data, "prid");

            // 更新场景
            if (isUpdate) {
                // 插入采购变更明细行标记
                boolean flag = false;

                Map<String, Object> prcMap = handlerService.findOneBySql("SELECT prnum from gq_prchange where id = " + prcId);

                if (prcMap != null && !prcMap.isEmpty()) {
                    String oldPrnum = MapUtils.getString(prcMap, "prnum");
                    String prNum = MapUtils.getString(data, "prnum");

                    if (!prNum.equals(oldPrnum)) {
                        flag = true;
                        // 删除采购变更历史行信息
                        handlerService.updateBySql("delete from gq_prlinehistory where prcid = " + prcId);
                        // 删除采购变更变更行信息
                        handlerService.updateBySql("delete from gq_prlinechange where prcid = " + prcId);
                    } else {
                        List<Map<String, Object>> prclList = handlerService.findBySql("SELECT id from gq_prlinehistory where prcid = " + prcId);
                        if (CollectionUtils.isEmpty(prclList)) {
                            flag = true;
                        }
                    }
                }

                if (flag) {
                    //插入采购申请行到采购变更历史信息
                    addPrcLine(prcId, prId);
                }
            }
        }
    }

    @Override
    public void afterSave(String app, String objName, Map<String, Object> data, boolean isUpdate) throws Exception {
        if (!isUpdate) {
            String prcId = MapUtils.getString(data, "id");
            String prId = MapUtils.getString(data, "prid");
            List<Map<String, Object>> bySql = handlerService.findBySql("select * from gq_prlinehistory where prcid='" + prcId + "'");
            if (CollectionUtils.isEmpty(bySql)){
                //插入采购申请行到采购变更历史信息
                addPrcLine(prcId, prId);
            }

        }
        String id = MapUtils.getString(data, "id");
        List<Map<String, Object>> bySql1 = handlerService.findBySql("select * from gq_prlinechange where prcid='" + id+"'");
        if (CollectionUtils.isNotEmpty(bySql1)){
            float A1totalcost =0;
            float A4totalcost =0;
            float A5totalcost =0;
            float A2totalcost=0;
            for(int i=0;i<bySql1.size();i++){
                Map<String, Object> map = bySql1.get(i);
                String itemtype = MapUtils.getString(map, "itemtype");
                Float linecost = MapUtils.getFloat(map, "linecost");
                if (itemtype.contains("A1类")){
                    A1totalcost=A1totalcost+linecost;
                }else if (itemtype.contains("A4类")){
                    A4totalcost=A4totalcost+linecost;
                }else if (itemtype.contains("A5类")){
                    A5totalcost = A5totalcost+linecost;
                }else if (itemtype.contains("A2类")){
                    A2totalcost =A2totalcost+linecost;
                }
            }
            handlerService.updateBySql("update gq_prchange set A1totalcost="+A1totalcost+",A4totalcost="+A4totalcost+",A5totalcost="+A5totalcost+",A2totalcost="+A2totalcost+" where id = " + id );
        }
    }

    @Override
    public String beforeExcuteFlow(String app, Long id, FlowNode flowNode, FlowOpinion op) throws Exception {
        Map<String, Object> map = op.getVariables();
        if ("submit".equals(flowNode.getId())) {
            // 查询采购变更明细行信息
            Map<String, Object> params = new HashMap<>(1);
            params.put("prcid", id);

            List<Map<String, Object>> list = handlerService.findList("gq_prlinechange", params);

            if (CollectionUtils.isEmpty(list)) {
                return "请编辑采购变更明细行数据!";
            }

            // 判断是否发生变更
            String result = judgeChange(id.intValue());

            if (StringUtils.isNotEmpty(result)) {
                return "采购申请行物料:" + result + "未发生变更, 请处理后再进行提交!";
            }
            //采购变更无法选择计划提交的数据
            List<Map<String, Object>> bySql = handlerService.findBySql("select * from gq_prchange where id='" + id + "'");
            if (CollectionUtils.isNotEmpty(bySql)){
                String prnum = MapUtils.getString(bySql.get(0), "prnum");//采购申请编号
                String prid = MapUtils.getString(bySql.get(0), "prid");//采购申请id
                List<Map<String, Object>> bySql1 = handlerService.findBySql("select * from gq_ppline t left join gq_purchaseplan a on t.ppid=a.id where t.prid='" + prid + "' and a.status!='新建' and a.status!='已作废'");
                if (CollectionUtils.isNotEmpty(bySql1)){
                    return "采购计划已提交，请重新选择";
                }
            }
            List<Map<String, Object>> sys_setting = handlerService.findBySql("select field8 from sys_setting");//系统设置控制开关
            if (CollectionUtils.isNotEmpty(sys_setting)){
                String field8 = MapUtils.getString(sys_setting.get(0), "field8");
                if (field8.equals("1")){
                    //平衡利库更改库存台账占用数量
                    avblbalanceBaTreasury(id);
                }
            }
        }
        if (flowNode.getId().startsWith("complete") && op.isFlag()){
            // 处理采购变更明细行信息
            dealPrChgInfo(id.intValue(), MapUtils.getInteger(map, "prid"));
        }
        return null;
    }


    @Override
    public void afterMoveNode(String app, Long id) throws Exception {
        //流程关闭释放占用库存
        avblbalanceafterMoveNodeTu(id);
        super.afterMoveNode(app, id);
    }

    @Override
    public void afterCloseFlow(String app, Long id, FlowNode flowNode, FlowOpinion op) throws Exception {
        //流程关闭释放占用库存
        avblbalanceafterMoveNodeTu(id);
        super.afterCloseFlow(app, id, flowNode, op);
    }


    /**
     * 处理采购变更明细行信息
     *
     * @param prcId
     */
    private String judgeChange(Integer prcId) throws Exception {
        // 查询采购变更明细行信息
        List<Map<String, Object>> prclList = handlerService.findBySql("select * from gq_prlinechange where prcid = " + prcId);
        // 查询采购变更申请明细行
        List<Map<String, Object>> prchList = handlerService.findBySql("select * from gq_prlinehistory where prcid = " + prcId);

        if (CollectionUtils.isNotEmpty(prchList)) {
            // 变更对比排除字段
            List<String> expFields = new ArrayList<>();
            expFields.add("id");
            expFields.add("createtime");
            expFields.add("createuser");
            expFields.add("createuserdesc");
            expFields.add("updatetime");
            expFields.add("updateuser");
            expFields.add("status");
            expFields.add("version");
            expFields.add("deptcode");
            expFields.add("deptid");
            expFields.add("prcid");
            expFields.add("prlid");

            // 组织变更信息并更新数据
            StringBuilder result = new StringBuilder();

            for (Map<String, Object> prchMap : prchList) {
                Integer prlhid = MapUtils.getInteger(prchMap, "prlid");

                for (Map<String, Object> prclMap : prclList) {
                    Integer prllId = MapUtils.getInteger(prclMap, "prlid");

                    if (prlhid.equals(prllId)) {
                        boolean flag = false;

                        for (Map.Entry<String, Object> prclEntry : prclMap.entrySet()) {
                            if (expFields.contains(prclEntry.getKey())) {
                                continue;
                            }

                            for (Map.Entry<String, Object> prchEntry : prchMap.entrySet()) {
                                if (prclEntry.getKey().equals(prchEntry.getKey()) && !prclEntry.getValue().equals(prchEntry.getValue())) {
                                    flag = true;
                                    break;
                                }
                            }
                        }

                        if (!flag) {
                            result.append(MapUtils.getString(prchMap, "itemnum")).append(",");
                        }
                        break;
                    }
                }
            }

            if (StringUtils.isNotEmpty(result)) {
                result.deleteCharAt(result.length() - 1);
                return result.toString();
            }
        }

        return null;
    }

    /**
     * 处理采购变更明细行信息
     *
     * @param prcId
     * @param prId
     */
    private void dealPrChgInfo(Integer prcId, Integer prId) throws Exception {
        // 查询采购变更明细行信息
        List<Map<String, Object>> prclList = handlerService.findBySql("select * from gq_prlinechange where prcid = " + prcId);
        // 查询采购申请明细行
        List<Map<String, Object>> prlList = handlerService.findBySql("select * from gq_prline where prid = " + prId);

        if (CollectionUtils.isNotEmpty(prlList)) {
            // 变更对比排除字段
            List<String> expFields = new ArrayList<>();
            expFields.add("id");
            expFields.add("createtime");
            expFields.add("createuser");
            expFields.add("createuserdesc");
            expFields.add("updatetime");
            expFields.add("updateuser");
            expFields.add("status");
            expFields.add("version");
            expFields.add("prcid");
            expFields.add("prlid");
            expFields.add("proid");
            expFields.add("taskid");
            expFields.add("deptcode");
            expFields.add("deptid");
            expFields.add("avblbalance");

            // 组织变更信息并更新数据
            boolean flag = false;
            for (Map<String, Object> prlMap : prlList) {
                Map<String, Object> updMap = judgeExist(prlMap, prclList, expFields);

                if (updMap != null && !updMap.isEmpty()) {
                    updMap.put("id", prlMap.get("id"));
                    // 更新采购申请行信息
                    handlerService.update("gq_prline", updMap);

                    flag = true;
                }
            }

            // 保存采购申请版本数据
            if (flag) {
                // 保存采购申请主信息
                String conditionStr = " id = " + prId;
                versionService.saveData("gq_pr", conditionStr, null);
                // 保存采购申请行信息
                conditionStr = " prid = " + prId;
                versionService.saveData("gq_prline", conditionStr, null);
            }

            // 变更完采购申请行里数量单价后，还有变更采购申请主表里的预算总价
            prlList = handlerService.findBySql("select linecost from gq_prline where prid = " + prId);
            if(CollectionUtils.isNotEmpty(prlList)){
                BigDecimal totalCost = new BigDecimal("0");
                for (Map<String, Object> prlMap : prlList) {
                    String lineCost = MapUtils.getString(prlMap, "linecost");
                    BigDecimal lineCostDec = new BigDecimal(lineCost);
                    totalCost = totalCost.add(lineCostDec);
                }

                // 更新采购申请总价
                Map<String, Object> updMap = new HashMap<>(2);
                updMap.put("id", prId);
                updMap.put("totalcost", totalCost);
                handlerService.update("gq_pr", updMap);
            }
        }
    }

    /**
     * 判断变更信息
     *
     * @param prlMap
     * @param prclList
     * @param expFields
     * @return
     */
    private Map<String, Object> judgeExist(Map<String, Object> prlMap, List<Map<String, Object>> prclList, List<String> expFields) {
        Integer id = MapUtils.getInteger(prlMap, "id");

        for (Map<String, Object> prclMap : prclList) {
            Integer prlId = MapUtils.getInteger(prclMap, "prlid");

            if (id.equals(prlId)) {
                Map<String, Object> chgMap = new HashMap<>();

                for (Map.Entry<String, Object> prclEntry : prclMap.entrySet()) {
                    if (expFields.contains(prclEntry.getKey())) {
                        continue;
                    }

                    for (Map.Entry<String, Object> prlEntry : prlMap.entrySet()) {
                        if (prclEntry.getKey().equals(prlEntry.getKey()) && !prclEntry.getValue().equals(prlEntry.getValue())) {
                            chgMap.put(prclEntry.getKey(), prclEntry.getValue());
                            break;
                        }
                    }
                }

                if (chgMap != null && !chgMap.isEmpty()) {
                    return chgMap;
                }
                break;
            }
        }

        return null;
    }

    /**
     * 插入采购申请行到采购申请行历史信息
     *
     * @param prcId 采购变更ID
     * @param prId 采购申请ID
     */
    private void addPrcLine(String prcId, String prId) throws Exception {
        // 查询采购申请行信息
        List<Map<String, Object>> list = handlerService.findBySql("select * from gq_prline where prid = " + prId);

        if (CollectionUtils.isNotEmpty(list)) {
            List<Map<String, Object>> insertList = new ArrayList<>(list.size());

            // 字段转换集合
            Map<String, List<String>> convertMap = new HashMap<>();
            convertMap.computeIfAbsent("id", key -> new ArrayList<>()).add("prlid");

            for (Map<String, Object> map : list) {
                // 数据转换
                Map<String, Object> addMap = tableService.convertMap("gq_prline", "gq_prlinehistory", map, convertMap, null);
                // 采购变更id
                addMap.put("prcid", prcId);
                addMap.put("ischange", 0);
                insertList.add(addMap);
            }

            // 批量插入采购申请行历史明细
            if (CollectionUtils.isNotEmpty(insertList)) {
                handlerService.batchInsert("prchangeApp","gq_prlinehistory", insertList);
            }
        }
    }

    @Override
    public Object operate(String app, String type, Map<String, Object> params) throws Exception {
        int page = 1;
        int limit = 10;
        if (MapUtils.getInteger(params, AppConstant.BIZ_QUERY_PAGE) != null && MapUtils.getInteger(params, AppConstant.BIZ_QUERY_LIMIT) != null) {
            page = MapUtil.getInt(params, AppConstant.BIZ_QUERY_PAGE);
            limit = MapUtil.getInt(params, AppConstant.BIZ_QUERY_LIMIT);
        }
        BaseHandler handler = null;
        LoginAppUser loginAppUser = SysUserUtil.getLoginAppUser();
        boolean isPermission = true;
        if(ToolUtil.isNotEmpty(params.get("isPermission"))){
            isPermission = MapUtil.getBool(params,"isPermission");
        }else{
            if (AppConstant.USER_ADMIN.equals(loginAppUser.getUsername())) {
                isPermission = false;
            }
            if (app.startsWith("tb")) {
                isPermission = false;
            }
        }
        BizApp appinfo = cfgRedisCache.getCacheApp(app);
        CommonModel commonModel = new CommonModel();
        BizObject object = cfgRedisCache.getCacheObjByApp(app);
        String handleclass = appinfo.getHandleclass();
        if (ToolUtil.isNotEmpty(handleclass)) {
            try {
                handler = HandlerFactory.getBeanHandler(handleclass);
            }catch (Exception e)
            {

            }
        }

        if ("prchangeApp".equals(app)&&"getProjectType".equals(type)){
            if (MapUtils.getInteger(params, "page") != null && MapUtils.getInteger(params, "limit") != null) {
                StringBuilder sb = new StringBuilder();
                // 查询数据字典信息
                List<Map<String, Object>> dictSql = handlerService.findBySql("select code, name from sys_dict where " +
                        " pid in (select id from sys_dict where code = 'pp_projecttype')");
                if (CollectionUtils.isNotEmpty(dictSql)) {
                    List<Map<String, Object>> collect = dictSql.stream().skip((page - 1) * limit).limit(limit).collect(Collectors.toList());
                    return PageResult.<Map<String, Object>>builder().data(collect).code(0).count((long) dictSql.size()).build();
                }
            }
        }else if("prchangeApp".equals(app) && "getPurchaseWay".equals(type)){
            String projecttype = MapUtils.getString(params, "projecttype");
            if ( StringUtils.isEmpty(projecttype)) {
                return null;
            }

            StringBuilder sb = new StringBuilder();
            switch (projecttype){
                case "政府采购" :
                    sb.append("'线下采购'");
                    break;
                case "非政府采购" :
                    sb.append("'线下采购','电商采购'");
                    break;
            }
            List<Map<String, Object>> dictSql = handlerService.findBySql("select code, name from sys_dict where pid in (select id from sys_dict where code = 'po_purchaseway') and  code in (" + sb + ")");
            if (CollectionUtils.isNotEmpty(dictSql)) {
                List<Map<String, Object>> collect = dictSql.stream().skip((page - 1) * limit).limit(limit).collect(Collectors.toList());
                return PageResult.<Map<String, Object>>builder().data(collect).code(0).count((long) dictSql.size()).build();
            }
        }else if ("prchangeApp".equals(app) && "getPurchaseType".equals(type)){
            String purchasecategory = MapUtils.getString(params, "purchasecategory");
            String projecttype = MapUtils.getString(params, "projecttype");
            String purchaseway = MapUtils.getString(params, "purchaseway");
            StringBuilder sb = new StringBuilder();
            switch (purchasecategory) {
                case "物资":
                    if ("政府采购".equals(projecttype) && "线下采购".equals(purchaseway)) {
                        sb.append("'公开招标','邀请招标','竞争性谈判','竞争性磋商','单一来源','询价','其他方式'");
                    } else if ("非政府采购".equals(projecttype)) {
                        switch (purchaseway) {
                            case "线下采购":
                                sb.append("'谈判','询比','直接采购','小额零星（直接实施）','小额零星（询价实施）'");
                                break;
                            case "电商采购":
                                sb.append("'直购','比选'");
                                break;
                            default:
                                break;
                        }

                    }
            }
            List<Map<String, Object>> dictSql = handlerService.findBySql("select code, name from sys_dict where " +
                    " pid in (select id from sys_dict where code = 'po_purchasetype')" +
                    " and code in (" + sb + ")");

            if (CollectionUtils.isNotEmpty(dictSql)) {
                List<Map<String, Object>> collect = dictSql.stream().skip((page - 1) * limit).limit(limit).collect(Collectors.toList());
                return PageResult.<Map<String, Object>>builder().data(collect).code(0).count((long) dictSql.size()).build();
            }
        }else if ("prchangeApp".equals(app)&& "Procurechanges".equals(type)){
            List<Map<String, Object>> bySql1 = handlerService.findBySql("SELECT * from biz_attribute where object_id ='832'");
            String columnname="";
            String string="";

            if (CollectionUtils.isNotEmpty(bySql1)){
                for (int i=0;i<bySql1.size();i++){
                    Map<String, Object> map = bySql1.get(i);
                    columnname = MapUtils.getString(map, "columnname");
                    //字段名_orders: "asc"
                    String columnname1= columnname+"_orders";
                    String columnname11 = MapUtils.getString(params, columnname1);

                    if (StringUtils.isNotEmpty(columnname11)) {
                        string  = "order by "+columnname+" "+columnname11;
                    }
                }
            }
            String prnum = MapUtils.getString(params, "prnum");//采购申请编号
            String prdesc = MapUtils.getString(params, "prdesc");//采购申请描述
            String userid = MapUtils.getString(params, "userid");//当前用户id
            List<Map<String, Object>> bySql = null;
            ArrayList<String> strings = new ArrayList<>();
            strings.add(prnum);
            strings.add("prnum");
            strings.add(prdesc);
            strings.add("prdesc");
            if (prnum==null&&prdesc==null){
                if (StringUtils.isEmpty(string)){
                    string ="order by t.applytime desc";
                }
                bySql = handlerService.findBySql("SELECT\n" +
                        "\tt.`subjectno`,\n" +
                        "\tt.`prdesc`,\n" +
                        "\tt.`proid`,\n" +
                        "\tt.`prnum`,\n" +
                        "\tt.`applyby`,\n" +
                        "\tt.`personincharge`,\n" +
                        "\tt.`proleader`,\n" +
                        "\tt.`id`,\n" +
                        "\tt.`type`,\n" +
                        "\tt.`applytime`,\n" +
                        "\tt.`deptcodedesc`,\n" +
                        "\tt.`taskid`,\n" +
                        "\tt.`createuserdesc`,\n" +
                        "\tprojectEntity.proname AS 'projectEntity_proname',\n" +
                        "\ttaskEntity.NAME AS 'taskEntity_name' \n" +
                        "FROM\n" +
                        "\t`gq_pr` t\n" +
                        "\tLEFT JOIN gq_proaccount projectEntity ON t.proid = projectEntity.id\n" +
                        "\tLEFT JOIN gq_scheduleplantask taskEntity ON t.taskid = taskEntity.id \n" +
                        "WHERE\n" +
                        "\tt.`delflag` = '0' \n" +
                        "\tAND NOT EXISTS (\n" +
                        "\tSELECT\n" +
                        "\t\t1 \n" +
                        "\tFROM\n" +
                        "\t\tgq_ppline ppl \n" +
                        "\tWHERE\n" +
                        "\t\tppl.ppid IN (  SELECT pp.id FROM gq_purchaseplan pp WHERE pp.STATUS !='新建' ) \n" +
                        "\t\tAND t.id = ppl.prid \n" +
                        "\t) \n" +
                        "\tAND t.STATUS = '已批准' and t.createuser='"+userid+"' "+string+"");
            }else {
                if (StringUtils.isEmpty(string)){
                    string ="order by t.applytime desc";
                }
                String list = " t.";
                String list1=" like";
                String list3="";
                //String list2="and";
                for (int i=0;i<strings.size();i=i+2){
                    String s = strings.get(i);
                    if (s!=null){
                        if(list3==""){
                            list3=list3+list+strings.get(i+1)+list1+"'%"+s+"%'";
                        }else{
                            list3=list3+" and "+list+strings.get(i+1)+list1+"'%"+s+"%'";
                        }
                    }
                }
                bySql = handlerService.findBySql("SELECT\n" +
                        "\tt.`subjectno`,\n" +
                        "\tt.`prdesc`,\n" +
                        "\tt.`proid`,\n" +
                        "\tt.`prnum`,\n" +
                        "\tt.`id`,\n" +
                        "\tt.`type`,\n" +
                        "\tt.`applytime`,\n" +
                        "\tt.`deptcodedesc`,\n" +
                        "\tt.`taskid`,\n" +
                        "\tt.`createuserdesc`,\n" +
                        "\tprojectEntity.proname AS 'projectEntity_proname',\n" +
                        "\ttaskEntity.NAME AS 'taskEntity_name' \n" +
                        "FROM\n" +
                        "\t`gq_pr` t\n" +
                        "\tLEFT JOIN gq_proaccount projectEntity ON t.proid = projectEntity.id\n" +
                        "\tLEFT JOIN gq_scheduleplantask taskEntity ON t.taskid = taskEntity.id \n" +
                        "WHERE\n" +
                        "\tt.`delflag` = '0' \n" +
                        "\tand "+list3+" and  NOT EXISTS (\n" +
                        "\tSELECT\n" +
                        "\t\t1 \n" +
                        "\tFROM\n" +
                        "\t\tgq_ppline ppl \n" +
                        "\tWHERE\n" +
                        "\t\tppl.ppid IN (  SELECT pp.id FROM gq_purchaseplan pp WHERE pp.STATUS !='新建' ) \n" +
                        "\t\tAND t.id = ppl.prid \n" +
                        "\t) \n" +
                        "\tAND t.STATUS = '已批准' and t.createuser='"+userid+"' "+string+"");
            }
            if (CollectionUtils.isNotEmpty(bySql)) {
                List<Map<String, Object>> collect = bySql.stream().skip((page - 1) * limit).limit(limit).collect(Collectors.toList());
                return PageResult.<Map<String, Object>>builder().data(collect).code(0).count((long) bySql.size()).build();
            }
            else {
                HashMap<String, Object> hashMap = new HashMap<>();
                List<Map<String, Object>> collect = bySql.stream().skip((page - 1) * limit).limit(limit).collect(Collectors.toList());
                hashMap.put("code",0);
                hashMap.put("count",0);
                hashMap.put("data",collect);
                return hashMap;
            }
        }else if ("prchangeApp".equals(app)&& "afterProcurechanges".equals(type)){
            String prid = MapUtils.getString(params, "prid");
            List<Map<String, Object>> bySql = handlerService.findBySql("select * from gq_prline where prid='" + prid + "'");
            if (CollectionUtils.isNotEmpty(bySql)){
                    List<Map<String, Object>> collect = bySql.stream().skip((page - 1) * limit).limit(limit).collect(Collectors.toList());
                    return PageResult.<Map<String, Object>>builder().data(collect).code(0).count((long) bySql.size()).build();
            }
        }
        return super.operate(app, type, params);
    }

    //平衡利库更改库存台账占用数量
    public void avblbalanceBaTreasury(Long id) throws Exception {
        List<Map<String, Object>> bySql = handlerService.findBySql("select prnum,applyby,subjectno,prid from gq_prchange where id='" + id + "'");
        if (CollectionUtils.isNotEmpty(bySql)){
            String subjectno = MapUtils.getString(bySql.get(0), "subjectno");//课题号
            String prnum = MapUtils.getString(bySql.get(0), "prnum");//采购申请编码
            String applyby = MapUtils.getString(bySql.get(0), "applyby");//需求人
            String prid = MapUtils.getString(bySql.get(0), "prid");//采购申请id
            avblbalanceafterMoveNode(prid,id);//根据子表数据去释放对应占用库存台账，并删除利库单
            List<Map<String, Object>> bySql1 = handlerService.findBySql("select * from gq_prlinechange where prcid='" + id + "'");
            if (CollectionUtils.isNotEmpty(bySql1)){
                for (int i=0;i<bySql1.size();i++) {
                    List<Map<String, Object>> hashMap = new ArrayList<>();//记录库存台账信息
                    Map<String, Object> map = bySql1.get(i);
                    String id1 = MapUtils.getString(map, "id");//申请变更航行id
                    String prlid = MapUtils.getString(map, "prlid");//采购申请航id
                    String itemnum = MapUtils.getString(map, "itemnum");//物料编码
                    String itemdesc = MapUtils.getString(map, "itemdesc");//物料名称
                    String model = MapUtils.getString(map, "model");//型号
                    String orderunit = MapUtils.getString(map, "orderunit");//计量单位
                    String unitcost = MapUtils.getString(map, "unitcost");//单价
                    if (unitcost == null || unitcost == "") {
                        unitcost = "0.0000";
                    }
                    BigDecimal bigDecimal = new BigDecimal(unitcost);
                    Float requirement = MapUtils.getFloat(map, "requirement");//需求数量
                    Float avblbalance = MapUtils.getFloat(map, "avblbalance");//可用数量
                    Float numusage = MapUtils.getFloat(map, "numusage");//占用量
//                    String ledreturndata = MapUtils.getString(map, "ledreturndata");//台账返回数据
                    //求总可用余量
                    String stu = "科研管理";
                    List<Map<String, Object>> bySql2 = handlerService.findBySql("SELECT \n" +
                            "    SUM(a.avblbalance) AS avblbalance \n" +
                            "FROM \n" +
                            "    gq_invbalances a \n" +
                            "WHERE \n" +
                            "    a.itemnum = '" + itemnum + "' \n" +
                            "    AND (a.subjectno = '" + subjectno + "' OR a.subjectno = '" + stu + "')\n");
                    if (CollectionUtils.isNotEmpty(bySql2)) {
                        Float avblbalance3 = MapUtils.getFloat(bySql2.get(0), "avblbalance");
                        if (avblbalance3!=null&&avblbalance3>0){
                        float orderqty = 0;//待采购量
                        // 如果可用余量大于等于需求量，待采购量为0；占用量（占用量总数）为需求量；
                        if (avblbalance3.compareTo(requirement) >= 0) {
                            numusage = requirement;//
                        } else {
                            // 如果可用余量小于需求量，则待采购量=需求量-可用余量；占用量（占用量总数）=需求量-待采购量
                            orderqty = requirement - avblbalance3;
                            numusage = requirement - orderqty;
                        }
                        BigDecimal bigDecimal1 = new BigDecimal(orderqty);
                        BigDecimal linecost = new BigDecimal("0");
                        linecost = bigDecimal.multiply(bigDecimal1);
                        handlerService.updateBySql("update gq_prlinechange set avblbalance=" + avblbalance3 + ",orderqty=" + orderqty + ",numusage=" + numusage + ",linecost=" + linecost + " where id='" + id1 + "'");

                    List<Map<String, Object>> bySql3 = handlerService.findBySql("select sum(t.avblbalance) as avblbalance from gq_invbalances t where t.itemnum='" + itemnum + "' and t.subjectno ='" + subjectno + "'");
                    if (CollectionUtils.isNotEmpty(bySql3)) {
                        Float avblbalance2 = MapUtils.getFloat(bySql3.get(0), "avblbalance");
                        if (avblbalance2==null){
                            avblbalance2=(float)0;
                        }
                        if (avblbalance2.compareTo(numusage) >= 0) {//判定在想通课题号下库存可用总量是否够占用
                            List<Map<String, Object>> bySql4 = handlerService.findBySql("select t.* from gq_invbalances t where t.itemnum='" + itemnum + "' and t.subjectno ='" + subjectno + "' and t.avblbalance>0 order by enddate,manbanumber,startdate,batchnum");
                            if (CollectionUtils.isNotEmpty(bySql4)) {
                                Float usagea = numusage;//给占用量赋值
                                for (int j = 0; j < bySql4.size(); j++) {
                                    Map<String, Object> map1 = bySql4.get(j);
                                    Float avblbalance1 = MapUtils.getFloat(map1, "avblbalance");//库存台账可用数量总量
                                    Float numusage1 = MapUtils.getFloat(map1, "numusage");//库存占用数量
                                    Float numusage3 = (float) 0;//利库单占用数量
                                    if (numusage1 == null) {
                                        numusage1 = (float) 0.00;
                                    }
                                    String id2 = MapUtils.getString(map1, "id");//库存余量表id
                                    if (usagea >= avblbalance1) {//用占用量进行比较
                                        numusage1 = numusage1 + avblbalance1;
                                        handlerService.updateBySql("update gq_invbalances set avblbalance=0,numusage=" + numusage1 + " where id='" + id2 + "'");
                                        usagea = usagea - avblbalance1;//占用去掉可用量
                                        //将占用人记录到库存台账中
                                        occurecordsBaTreasury(id2,applyby,avblbalance1,id1);
                                        //记录到采购申请字表中
                                        ledreturndataBaTreasury(id2, hashMap, avblbalance1);
                                        //生成利库单
                                        numusage3 = avblbalance1;
                                        generatetreanoteBaTreasury(prid, id2, prnum, applyby, itemnum, itemdesc, model, orderunit, numusage3);
                                        //生成出库单
                                        generaoutorderBaTreasury(id,avblbalance1,id2,id1);
                                    } else {
                                        //在库存可用量大于占用量时
                                        float avblbalance4 = 0;
                                        avblbalance4 = avblbalance1 - usagea;
                                        numusage1 = numusage1 + usagea;
                                        handlerService.updateBySql("update gq_invbalances set avblbalance=" + avblbalance4 + ",numusage=" + numusage1 + " where id='" + id2 + "'");
                                        //将占用人记录到库存台账中
                                        occurecordsBaTreasury(id2,applyby,usagea,id1);
                                        //记录到采购申请字表中
                                        ledreturndataBaTreasury(id2, hashMap, avblbalance1);
                                        //生成利库单
                                        numusage3 = usagea;
                                        generatetreanoteBaTreasury(prid, id2, prnum, applyby, itemnum, itemdesc, model, orderunit, numusage3);
                                        //生成出库单
                                        generaoutorderBaTreasury(id,avblbalance1,id2,id1);
                                        break;
                                    }
                                }
                            }
                        } else if (avblbalance2 > 0 && avblbalance2.compareTo(numusage) < 0) {
                            Float usagea = numusage;//给占用量赋值
                            List<Map<String, Object>> bySql4 = handlerService.findBySql("select t.* from gq_invbalances t where t.itemnum='" + itemnum + "' and t.subjectno ='" + subjectno + "' and t.avblbalance>0 order by enddate,manbanumber,startdate,batchnum");
                            if (CollectionUtils.isNotEmpty(bySql4)) {
                                for (int j = 0; j < bySql3.size(); j++) {
                                    Map<String, Object> map1 = bySql4.get(j);
                                    String id2 = MapUtils.getString(map1, "id");//库存余量表id
                                    Float avblbalance1 = MapUtils.getFloat(map1, "avblbalance");//库存台账可用数量总量
                                    Float numusage1 = MapUtils.getFloat(map1, "numusage");//库存占用数量
                                    Float numusage3 = (float) 0;//利库单占用数量
                                    if (numusage1 == null) {
                                        numusage1 = (float) 0.00;
                                    }
                                    usagea = usagea - avblbalance1;
                                    numusage1 = numusage1 + avblbalance1;
                                    //可用于量小于占用量时
                                    handlerService.updateBySql("update gq_invbalances set avblbalance=0,numusage=" + numusage1 + " where id='" + id2 + "'");
                                    //将占用人记录到库存台账中
                                    occurecordsBaTreasury(id2,applyby,avblbalance1,id1);
                                    //记录到采购申请字表中
                                    ledreturndataBaTreasury(id2, hashMap, avblbalance1);
                                    //生成利库单
                                    numusage3 = avblbalance1;
                                    generatetreanoteBaTreasury(prid, id2, prnum, applyby, itemnum, itemdesc, model, orderunit, numusage3);
                                    //生成出库单
                                    generaoutorderBaTreasury(id,avblbalance1,id2,id1);
                                }
                            }
                            //String stun ="科研管理";
                            List<Map<String, Object>> bySql5 = handlerService.findBySql("select t.* from gq_invbalances t where t.itemnum='" + itemnum + "' and t.subjectno ='" + stu + "'and t.avblbalance>0 order by enddate,manbanumber,startdate,batchnum");
                            if (CollectionUtils.isNotEmpty(bySql5)) {
                                for (int z = 0; z < bySql5.size(); z++) {
                                    Map<String, Object> map1 = bySql5.get(z);
                                    Float avblbalance1 = MapUtils.getFloat(map1, "avblbalance");//科研管理课题号下的物料可用数量
                                    Float numusage1 = MapUtils.getFloat(map1, "numusage");//库存占用数
                                    Float numusage3 = (float) 0;//利库单占用数量
                                    if (numusage1 == null) {
                                        numusage1 = (float) 0.00;
                                    }
                                    String id2 = MapUtils.getString(map1, "id");
                                    if (usagea >= avblbalance1) {
                                        numusage1 = numusage1 + avblbalance1;
                                        handlerService.updateBySql("update gq_invbalances set avblbalance=0,numusage=" + numusage1 + " where id='" + id2 + "'");
                                        usagea = usagea - avblbalance1;//占用去掉可用量
                                        //将占用人记录到库存台账中
                                        occurecordsBaTreasury(id2,applyby,avblbalance1,id1);
                                        //记录到采购申请字表中
                                        ledreturndataBaTreasury(id2, hashMap, avblbalance1);
                                        //生成利库单
                                        numusage3 = avblbalance1;
                                        generatetreanoteBaTreasury(prid, id2, prnum, applyby, itemnum, itemdesc, model, orderunit, numusage3);
                                    } else {
                                        //在库存可用量大于占用量时
                                        float avblbalance4 = 0;
                                        avblbalance4 = avblbalance1 - usagea;
                                        numusage1 = numusage1 + usagea;
                                        handlerService.updateBySql("update gq_invbalances set avblbalance=" + avblbalance4 + ",numusage=" + numusage1 + " where id='" + id2 + "'");
                                        //将占用人记录到库存台账中
                                        occurecordsBaTreasury(id2,applyby,usagea,id1);
                                        //记录到采购申请字表中
                                        ledreturndataBaTreasury(id2, hashMap, avblbalance1);
                                        //生成利库单
                                        numusage3 = usagea;
                                        generatetreanoteBaTreasury(prid, id2, prnum, applyby, itemnum, itemdesc, model, orderunit, numusage3);
                                        break;
                                    }
                                }
                            }
                        } else if (avblbalance2 == 0) {
                            //当同课题号下物料可用数量不够则占用科研管理课题下物料
//                                List<Map<String, Object>> bySql3 = handlerService.findBySql("select t.* from gq_invbalances t where t.itemnum='" + itemnum + "' and t.subjectno ='" + subjectno + "' and t.avblbalance>0 order by enddate,manbanumber,startdate,batchnum");
//                                Float usagea =numusage;//给占用量赋值
//                                if (CollectionUtils.isNotEmpty(bySql3)){
//                                    for (int j=0;j<bySql3.size();j++){
//                                        Map<String, Object> map1 = bySql3.get(j);
//                                        Float avblbalance1 = MapUtils.getFloat(map1, "avblbalance");//库存台账可用数量总量
//                                        String id1 = MapUtils.getString(map1, "id");//库存余量表id
//                                        handlerService.updateBySql("update gq_invbalances set avblbalance=0,numusage="+avblbalance1+" where id='"+id1+"'");
//                                        usagea=usagea-avblbalance1;//占用去掉可用量
//                                    }
                            List<Map<String, Object>> bySql5 = handlerService.findBySql("select t.* from gq_invbalances t where t.itemnum='" + itemnum + "' and t.subjectno ='" + stu + "'and t.avblbalance>0 order by enddate,manbanumber,startdate,batchnum");
                            if (CollectionUtils.isNotEmpty(bySql5)) {
                                Float usagea = numusage;//给占用量赋值
                                for (int z = 0; z < bySql5.size(); z++) {
                                    Map<String, Object> map1 = bySql5.get(z);
                                    Float avblbalance1 = MapUtils.getFloat(map1, "avblbalance");//科研管理课题号下的物料可用数量
                                    Float numusage1 = MapUtils.getFloat(map1, "numusage");//库存占用数
                                    Float numusage3 = (float) 0;//利库单占用数量
                                    if (numusage1 == null) {
                                        numusage1 = (float) 0.00;
                                    }
                                    String id2 = MapUtils.getString(map1, "id");
                                    if (usagea >= avblbalance1) {
                                        numusage1 = numusage1 + avblbalance1;
                                        handlerService.updateBySql("update gq_invbalances set avblbalance=0,numusage=" + numusage1 + " where id='" + id2 + "'");
                                        usagea = usagea - avblbalance1;//占用去掉可用量
                                        //将占用人记录到库存台账中
                                        occurecordsBaTreasury(id2,applyby,avblbalance1,id1);
                                        //记录到采购申请字表中
                                        ledreturndataBaTreasury(id2, hashMap, avblbalance1);
                                        //生成利库单
                                        numusage3 = avblbalance1;
                                        generatetreanoteBaTreasury(prid, id2, prnum, applyby, itemnum, itemdesc, model, orderunit, numusage3);
                                    } else {
                                        //在库存可用量大于占用量时
                                        float avblbalance4 = 0;
                                        avblbalance4 = avblbalance1 - usagea;
                                        numusage1 = numusage1 + usagea;
                                        handlerService.updateBySql("update gq_invbalances set avblbalance=" + avblbalance4 + ",numusage=" + numusage1 + " where id='" + id2 + "'");
                                        //将占用人记录到库存台账中
                                        occurecordsBaTreasury(id2,applyby,usagea,id1);
                                        //记录到采购申请字表中
                                        ledreturndataBaTreasury(id2, hashMap, avblbalance1);
                                        //生成利库单
                                        numusage3 = usagea;
                                        generatetreanoteBaTreasury(prid, id2, prnum, applyby, itemnum, itemdesc, model, orderunit, numusage3);
                                        break;
                                    }
                                }
                            }
                        }

                        //返回到采购申请字表中
                        StringBuilder stringBuilder = new StringBuilder();
                        for (Map<String, Object> hashMapList : hashMap) {
                            for (Map.Entry<String, Object> entry : hashMapList.entrySet()) {
                                String key = entry.getKey();
                                Object value = entry.getValue();
                                stringBuilder.append(key).append(": ").append(value).append(", ");
                            }
                        }
//                        // 在每个HashMap之间添加换行符
//                        stringBuilder.append(System.lineSeparator());
                        String ledreturndata = stringBuilder.toString();
                        handlerService.updateBySql("update gq_prlinechange set ledreturndata='" + ledreturndata + "' where id='" + id1 + "'");
                    }
                }else {
                            BigDecimal bigDecimal1 = new BigDecimal(requirement);
                            BigDecimal linecost = new BigDecimal("0");
                            linecost = bigDecimal.multiply(bigDecimal1);
                            handlerService.updateBySql("update gq_prlinechange set avblbalance=0,orderqty=" + requirement + ",numusage=0,linecost=" + linecost + " where id='" + id1 + "'");
                    }

                    }
                }
            }
        }
    }

    //更改采购申请子表释放占用库存台账并删除利库单
    public void avblbalanceafterMoveNode(String prid,Long id) throws Exception {
        List<Map<String, Object>> sys_setting = handlerService.findBySql("select field8 from sys_setting");//系统设置控制开关
        if (CollectionUtils.isNotEmpty(sys_setting)) {
            String field8 = MapUtils.getString(sys_setting.get(0), "field8");
            if (field8.equals("1")) {
                List<Map<String, Object>> bySql4 = handlerService.findBySql("select * from gq_prchange where id='" + id + "'");
                String applyby = MapUtils.getString(bySql4.get(0), "applyby");
                handlerService.updateBySql("delete from gq_treasurybill where treasurybillid = '"+prid+"'");
                List<Map<String, Object>> bySql = handlerService.findBySql("select ledreturndata,id,prlid from gq_prlinechange where prcid='" + id + "' and numusage>0");
                if (CollectionUtils.isNotEmpty(bySql)){
                    for (int i=0;i<bySql.size();i++){
                        Map<String, Object> map = bySql.get(i);
                        String id1 = MapUtils.getString(map, "id");
                        String prlid = MapUtils.getString(map, "prlid");
                        String ledreturndata = MapUtils.getString(map, "ledreturndata");
                        String[] keyValuePairs = ledreturndata.split(", ");//将拼接后的字符串以适当的分隔符进行拆分
                        List<HashMap<String, String>> hashMapList = new ArrayList<>();
                        for (String keyValuePair : keyValuePairs) {
                            HashMap<String, String> hashMap = new HashMap<>();
                            String[] keyValue = keyValuePair.split(": ");
                            String key = keyValue[0];
                            String value = keyValue[1];
                            String cleanedStr = value.replace(",", "");
                            hashMap.put(key, cleanedStr);
                            hashMapList.add(hashMap);
                        }
                        //获取map中的库存余量表id和库存更改数量
                        for (Map<String, String> mapList : hashMapList) {
                            for (Map.Entry<String, String> entry : mapList.entrySet()) {
                                String key = entry.getKey();//库存余量id
                                String value =  entry.getValue();//库存更改数量
                                float floatValue = 0.0f;
                                try {
                                    floatValue = Float.parseFloat(value);
                                } catch (NumberFormatException e) {
                                    // 处理转换异常
                                }
                                List<Map<String, Object>> bySql2 = handlerService.findBySql("select * from gq_invbalances where id='" + key + "'");
                                if (CollectionUtils.isNotEmpty(bySql2)){
                                    Float avblbalance1 = MapUtils.getFloat(bySql2.get(0),"avblbalance");
                                    Float numusage1 = MapUtils.getFloat(bySql2.get(0),"numusage");
//                                    String occurecords = MapUtils.getString(bySql2.get(0), "occurecords");
                                    avblbalance1=avblbalance1+floatValue;
                                    numusage1=numusage1-floatValue;
//                                    List<Map<String,String>> maps= new ArrayList<>();
//                                    StringBuilder stringBuilder = new StringBuilder();
                                    if (floatValue>0){
//                                        String[] occurecordss = occurecords.split(", ");//将拼接后的字符串以适当的分隔符进行拆分
//                                        for (String keyValuePair : occurecordss) {
//                                            HashMap<String, String> hashMap = new HashMap<>();
//                                            String[] keyValue = keyValuePair.split(": ");
//                                            String key1 = keyValue[0];
//                                            String value1 = keyValue[1];
//                                            String cleanedStr = value1.replace(",", "");
//                                            if (key1.equals(applyby)){
//
//                                            }else {
//                                                hashMap.put(key1, cleanedStr);
//                                                maps.add(hashMap);
//                                            }
//                                        }
//                                        for (Map<String, String> hashMapList1 : maps) {
//                                            for (Map.Entry<String, String> entry1 : hashMapList1.entrySet()) {
//                                                String key2 = entry1.getKey();
//                                                Object value2 = entry1.getValue();
//                                                stringBuilder.append(key2).append(": ").append(value2).append(", ");
//                                            }
//                                        }
                                        List<Map<String, Object>> bySql3 = handlerService.findBySql("select * from gq_occurecords where invbalancesid='" + key + "' and prid='" + prlid + "' and applyby='"+applyby+"'");
                                        if (CollectionUtils.isNotEmpty(bySql3)){
                                            for (int y=0;y<bySql3.size();y++){
                                                Float numusage2 = MapUtils.getFloat(bySql3.get(y), "numusage");
                                                String id2 = MapUtils.getString(bySql3.get(y), "id");
                                                numusage2=numusage2-floatValue;
                                                if (numusage2<=0){
                                                    handlerService.updateBySql("DELETE FROM gq_occurecords WHERE id = '"+id2+"'");
                                                }
                                            }
                                        }
                                    }
//                                    String occurecordss = stringBuilder.toString();
                                    handlerService.updateBySql("update gq_invbalances set avblbalance="+avblbalance1+",numusage="+numusage1+" where id='"+key+"'");
                                }
                            }
                        }
                    }
                    String str="";
                    handlerService.updateBySql("update gq_prlinechange set ledreturndata='"+str+"' where prcid='" + id + "'");
                }

            }
        }
    }
    //生成出库申请单
    private void generaoutorderBaTreasury(Long id,Float avblbalance1,String id2,String id1)throws Exception{
        List<Map<String, Object>> bySql = handlerService.findBySql("select * from gq_prchange where id='" + id + "'");
        if (CollectionUtils.isNotEmpty(bySql)){
            String prdesc = MapUtils.getString(bySql.get(0), "prcdesc");//申请描述
            String status="新建";
            String applyby = MapUtils.getString(bySql.get(0), "applyby");
            List<Map<String, Object>> bySql4 = handlerService.findBySql("select name from gq_person where id='" + applyby + "'");
            String name = MapUtils.getString(bySql4.get(0), "name");
            String subjectno = MapUtils.getString(bySql.get(0), "subjectno");//课题号
            String proid = MapUtils.getString(bySql.get(0), "proid");//项目编号
            String deptcode = MapUtils.getString(bySql.get(0), "deptcode");//部门
            String createuserdesc = MapUtils.getString(bySql.get(0), "createuserdesc");//创建人
            String createuser = MapUtils.getString(bySql.get(0), "createuser");//创建人id
            String taskid = MapUtils.getString(bySql.get(0), "taskid");//任务id
            List<Map<String, Object>> bySql1 = handlerService.findBySql("select * from gq_invbalances where id='" + id2 + "'");
            if (CollectionUtils.isNotEmpty(bySql1)){
                String storenum = MapUtils.getString(bySql1.get(0), "storenum");//库房编码
                List<Map<String, Object>> bySql3 = handlerService.findBySql("select storedesc from gq_store where storenum='" + storenum + "'");
                String storedesc = MapUtils.getString(bySql3.get(0), "storedesc");//库房名称
                String batchnum = MapUtils.getString(bySql1.get(0), "batchnum");//批次
                String binnum = MapUtils.getString(bySql1.get(0), "binnum");//货位
                List<Map<String, Object>> bySql2 = handlerService.findBySql("select * from gq_prlinechange where id='" + id1 + "'");
                if (CollectionUtils.isNotEmpty(bySql2)){
                    String itemnum = MapUtils.getString(bySql2.get(0), "itemnum");//物料编码
                    String itemdesc = MapUtils.getString(bySql2.get(0), "itemdesc");//物料名称
                    String model = MapUtils.getString(bySql2.get(0), "model");//型号
                    String orderunit = MapUtils.getString(bySql2.get(0), "orderunit");//计量单位
                    String unitcost = MapUtils.getString(bySql2.get(0), "unitcost");//单价
                    String specifications = MapUtils.getString(bySql2.get(0), "specifications");//规格
                    HashMap<String, Object> hashMap = new HashMap<>();
                    handlerService.addBizNum("itemapplyApp", "gq_itemapply", hashMap);//出库申请编码
                    String itemapplynum = (String) hashMap.get("itemapplynum");
                    HashMap<String, Object> hashMap1 = new HashMap<>();
                    hashMap1.put("itemapplynum",itemapplynum);
                    hashMap1.put("status",status);
                    hashMap1.put("applicantid",applyby);
                    hashMap1.put("applicant",name);
                    hashMap1.put("location",storenum);
                    hashMap1.put("createtime",new Date());
                    hashMap1.put("deptcode",deptcode);
                    hashMap1.put("locadesc",storedesc);
                    hashMap1.put("subjectno",subjectno);
                    hashMap1.put("proid",proid);
                    hashMap1.put("createuserdesc",createuserdesc);
                    hashMap1.put("createuser",createuser);
                    hashMap1.put("taskid",taskid);
                    handlerService.insert("gq_itemapply",hashMap1);
                    HashMap<String, Object> hashMap2 = new HashMap<>();
                    hashMap2.put("glnum",itemapplynum);
                    hashMap2.put("itemnum",itemnum);
                    hashMap2.put("Orunit",orderunit);
                    hashMap2.put("deptcode",deptcode);
                    hashMap2.put("createuser",createuser);
                    hashMap2.put("unitcost",unitcost);
                    hashMap2.put("specifications",specifications);
                    hashMap2.put("binnum",binnum);
                    hashMap2.put("lotnum",batchnum);
                    hashMap2.put("proid",proid);
                    hashMap2.put("taskid",taskid);
                    hashMap2.put("subjectno",subjectno);
                    hashMap2.put("locations",storenum);
                    handlerService.insert("gq_itemapplyline",hashMap2);
                }
            }
        }
    }
    //驳回，关闭流程，撤回按钮
    private void avblbalanceafterMoveNodeTu(Long id) throws Exception {
        List<Map<String, Object>> sys_setting = handlerService.findBySql("select field8 from sys_setting");//系统设置控制开关
        if (CollectionUtils.isNotEmpty(sys_setting)){
            String field8 = MapUtils.getString(sys_setting.get(0), "field8");
            if (field8.equals("1")){
                List<Map<String, Object>> bySql = handlerService.findBySql("select * from gq_prchange where id='" + id + "'");
                if (CollectionUtils.isNotEmpty(bySql)) {
                    String prid = MapUtils.getString(bySql.get(0), "prid");//爱狗申请id
                    String applyby = MapUtils.getString(bySql.get(0), "applyby");
                    handlerService.updateBySql("delete from gq_treasurybill where treasurybillid = '" + prid + "'");//删除利库单
                    List<Map<String, Object>> bySql1 = handlerService.findBySql("select * from gq_prlinechange where prid='" + id + "' and numusage>0");
                    if(CollectionUtils.isNotEmpty(bySql1)){
                        for (int i=0;i<bySql1.size();i++){
                            Map<String, Object> map = bySql1.get(i);
                            String ledreturndata = MapUtils.getString(map, "ledreturndata");//库存台账返回数据
                            String id1 = MapUtils.getString(map, "id");//采购申请变更行id
                            String prlid = MapUtils.getString(map, "prlid");
                            String str="";
                            handlerService.updateBySql("update gq_prlinechange set avblbalance=0,numusage=0,orderqty=0,linecost=0,ledreturndata='"+str+"' where id='"+id1+"'");
                            String[] keyValuePairs = ledreturndata.split(", ");//将拼接后的字符串以适当的分隔符进行拆分
                            List<HashMap<String, String>> hashMapList = new ArrayList<>();
                            for (String keyValuePair : keyValuePairs) {
                                HashMap<String, String> hashMap = new HashMap<>();
                                String[] keyValue = keyValuePair.split(": ");
                                String key = keyValue[0];
                                String value = keyValue[1];
                                String cleanedStr = value.replace(",", "");
                                hashMap.put(key, cleanedStr);
                                hashMapList.add(hashMap);
                            }
                            //获取map中的库存余量表id和库存更改数量
                            for (Map<String, String> mapList : hashMapList) {
                                for (Map.Entry<String, String> entry : mapList.entrySet()) {
                                    String key = entry.getKey();//库存余量id
                                    String value =  entry.getValue();//库存更改数量
                                    float floatValue = 0.0f;
                                    try {
                                        floatValue = Float.parseFloat(value);
                                    } catch (NumberFormatException e) {
                                        // 处理转换异常
                                    }
                                    List<Map<String, Object>> bySql2 = handlerService.findBySql("select * from gq_invbalances where id='" + key + "'");
                                    if (CollectionUtils.isNotEmpty(bySql2)){
                                        Float avblbalance1 = MapUtils.getFloat(bySql2.get(0),"avblbalance");
                                        Float numusage1 = MapUtils.getFloat(bySql2.get(0),"numusage");
//                                        String occurecords = MapUtils.getString(bySql2.get(0), "occurecords");
                                        avblbalance1=avblbalance1+floatValue;
                                        numusage1=numusage1-floatValue;
//                                        List<Map<String,String>> maps= new ArrayList<>();
//                                        StringBuilder stringBuilder = new StringBuilder();
                                        if (floatValue>0){
//                                            String[] occurecordss = occurecords.split(", ");//将拼接后的字符串以适当的分隔符进行拆分
//                                            for (String keyValuePair : occurecordss) {
//                                                HashMap<String, String> hashMap = new HashMap<>();
//                                                String[] keyValue = keyValuePair.split(": ");
//                                                String key1 = keyValue[0];
//                                                String value1 = keyValue[1];
//                                                String cleanedStr = value1.replace(",", "");
//                                                if (key1.equals(applyby)){
//
//                                                }else {
//                                                    hashMap.put(key1, cleanedStr);
//                                                    maps.add(hashMap);
//                                                }
//                                            }
//                                            for (Map<String, String> hashMapList1 : maps) {
//                                                for (Map.Entry<String, String> entry1 : hashMapList1.entrySet()) {
//                                                    String key2 = entry1.getKey();
//                                                    Object value2 = entry1.getValue();
//                                                    stringBuilder.append(key2).append(": ").append(value2).append(", ");
//                                                }
//                                            }
                                            List<Map<String, Object>> bySql3 = handlerService.findBySql("select * from gq_occurecords where invbalancesid='" + key + "' and prid='" + prlid + "' and applyby='"+applyby+"'");
                                            if (CollectionUtils.isNotEmpty(bySql3)){
                                                for (int y=0;y<bySql3.size();y++){
                                                    Float numusage2 = MapUtils.getFloat(bySql3.get(y), "numusage");
                                                    String id2 = MapUtils.getString(bySql3.get(y), "id");
                                                    numusage2=numusage2-floatValue;
                                                    if (numusage2<=0){
                                                        handlerService.updateBySql("DELETE FROM gq_occurecords WHERE id = '"+id2+"'");
                                                    }
                                                }
                                            }
                                        }
                                        handlerService.updateBySql("update gq_invbalances set avblbalance="+avblbalance1+",numusage="+numusage1+" where id='"+key+"'");
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    //记录库存台账信息
    private void ledreturndataBaTreasury(String id2,List<Map<String,Object>> hashMap,Float avblbalance1) throws Exception {
        List<Map<String, Object>> bySql = handlerService.findBySql("select curbal,avblbalance,numusage from gq_invbalances where id='" + id2 + "'");
        if (CollectionUtils.isNotEmpty(bySql)){
            Float curbal = MapUtils.getFloat(bySql.get(0), "curbal");
            Float avblbalance3 = MapUtils.getFloat(bySql.get(0), "avblbalance");
            //  Float numusage2 = MapUtils.getFloat(bySql.get(0), "numusage");
            float avblbalance4=0;
            avblbalance4 =avblbalance1-avblbalance3; //库存变更数量
            HashMap<String, Object> hashMap1 = new HashMap<>();
            hashMap1.put(id2,avblbalance4);
            hashMap.add(hashMap1);
        }
    }

    //生成利库单
    private void generatetreanoteBaTreasury(String prid,String id2,String prnum,String applyby,String itemnum,String itemdesc,String model,String orderunit,Float numusage3) throws Exception {
        List<Map<String, Object>> bySql = handlerService.findBySql("select batchnum from gq_invbalances where id='" + id2 + "'");
        if (CollectionUtils.isNotEmpty(bySql)){
            List<Map<String, Object>> bySql1 = handlerService.findBySql("select deptcode,createuser,subjectno from gq_pr where id='" + prid + "'");
            if (CollectionUtils.isNotEmpty(bySql1)){
                String subjectno = MapUtils.getString(bySql1.get(0), "subjectno");
                String deptcode = MapUtils.getString(bySql1.get(0), "deptcode");//部门
                String createuser = MapUtils.getString(bySql1.get(0), "createuser");//创建人
                String batchnum = MapUtils.getString(bySql.get(0), "batchnum");//库存批次
                HashMap<String, Object> hashMap = new HashMap<>();
                hashMap.put("prnum",prnum);//申请编号
                hashMap.put("subjectno",subjectno);
                hashMap.put("treasurybillid",prid);
                hashMap.put("applyby",applyby);
                hashMap.put("itemnum",itemnum);
                hashMap.put("itemdesc",itemdesc);
                hashMap.put("model",model);
                hashMap.put("orderunit",orderunit);
                hashMap.put("numusage",numusage3);
                hashMap.put("batchnum",batchnum);
                hashMap.put("deptcode",deptcode);
                hashMap.put("createuser",createuser);
                handlerService.insert("gq_treasurybill", hashMap);
            }

        }
    }

    // 将占用人记录到库存台账中
    private void occurecordsBaTreasury(String id2,String applyby,Float numusage1,String id1) throws Exception {
//        List<Map<String,String>> maps= new ArrayList<>();
//        String floatString1 = String.valueOf(numusage1);
//        List<Map<String, Object>> bySql = handlerService.findBySql("select occurecords from gq_invbalances where id='" + id2 + "'");
//        if (CollectionUtils.isNotEmpty(bySql)){
//            String occurecords = MapUtils.getString(bySql.get(0), "occurecords");
//            if (occurecords!=null&&occurecords!=""){
//                String[] keyValuePairs = occurecords.split(", ");//将拼接后的字符串以适当的分隔符进行拆分
//                for (String keyValuePair : keyValuePairs) {
//                    HashMap<String, String> hashMap = new HashMap<>();
//                    String[] keyValue = keyValuePair.split(": ");
//                    String key = keyValue[0];
//                    String value = keyValue[1];
//                    String cleanedStr = value.replace(",", "");
//                    hashMap.put(key,cleanedStr);
//                    maps.add(hashMap);
//                }
//                HashMap<String, String> hashMap1 = new HashMap<>();
//                hashMap1.put(applyby, floatString1);
//                maps.add(hashMap1);
//                StringBuilder stringBuilder = new StringBuilder();
//                for (Map<String, String> hashMapList : maps) {
//                    for (Map.Entry<String, String> entry : hashMapList.entrySet()) {
//                        String key = entry.getKey();
//                        Object value = entry.getValue();
//                        stringBuilder.append(key).append(": ").append(value).append(", ");
//                    }
//                }
//                String occurecordss = stringBuilder.toString();
//                handlerService.updateBySql("update gq_invbalances set occurecords='"+occurecordss+"' where id='"+id2+"'");
//            }else {
//                HashMap<String, String> hashMap1 = new HashMap<>();
//                hashMap1.put(applyby, floatString1);
//                maps.add(hashMap1);
//                StringBuilder stringBuilder = new StringBuilder();
//                for (Map<String, String> hashMapList : maps) {
//                    for (Map.Entry<String, String> entry : hashMapList.entrySet()) {
//                        String key = entry.getKey();
//                        Object value = entry.getValue();
//                        stringBuilder.append(key).append(": ").append(value).append(", ");
//                    }
//                }
//                String occurecords1 = stringBuilder.toString();
//                handlerService.updateBySql("update gq_invbalances set occurecords='"+occurecords1+"' where id='"+id2+"'");
//            }
//
//        }
        HashMap<String, Object> hashMap = new HashMap<>();
        hashMap.put("invbalancesid",id2);
        hashMap.put("applyby",applyby);
        hashMap.put("numusage",numusage1);
        hashMap.put("prid",id1);
        handlerService.insert("gq_occurecords", hashMap);
    }

    }
